using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using SalesAndStoreApp.DAL.DAO;

namespace SalesAndStoreApp.DAL.Gateway
{
    public class ProductGateway:DbGateway 
    {
        private InventoryGateway inventoryGateway = new InventoryGateway();

        public List<Product> GetAll()
        {
            try
            {
                Connection.Open();
                string query = "SELECT * FROM Product";
                Command.CommandText = query;
                List<Product> products = new List<Product>();
                using (SqlDataReader dataReader = Command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        var product = new Product
                        {
                            Id = int.Parse(dataReader["Id"].ToString()),
                            Name = dataReader["Name"].ToString(),
                            BuyUnitPrice = double.Parse(dataReader["BuyUnitPrice"].ToString()),
                            SaleUnitPrice = double.Parse(dataReader["SaleUnitPrice"].ToString().ToLower() == "" ? "0" : dataReader["SaleUnitPrice"].ToString())

                        };
                        products.Add(product);
                    }
                }
                Connection.Close();

                return products;
            }
            catch (Exception exception)
            { 
                throw new Exception("Error occurred while trying to retrieve all products!",exception);
            }
            
            
        }

        public Product GetById(int id)
        {
            try
            {
                string query = "SELECT * FROM Product WHERE Id =@id";
            Command.CommandText = query;
            Command.Parameters.Clear();
            Command.Parameters.Add("@id", SqlDbType.Int);
            Command.Parameters["@id"].Value = id;
            Product product = new Product();
            Connection.Open();
            using (SqlDataReader dataReader = Command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    product.Id = int.Parse(dataReader["Id"].ToString());
                    product.Name = dataReader["Name"].ToString();
                    product.BuyUnitPrice = double.Parse(dataReader["BuyUnitPrice"].ToString());
                    product.SaleUnitPrice =
                        double.Parse(dataReader["SaleUnitPrice"].ToString() == ""
                                         ? "0"
                                         : dataReader["SaleUnitPrice"].ToString());
                }
                Connection.Close();
                product.Inventory = inventoryGateway.GetInventoryOfProduct(product.Id);
                return product;
            }
            }
            catch (Exception exception)
            {
                throw new Exception("Error occurred while trying to retrieve a product", exception);
            }
            
           
        }

        public bool SaveOrUpdate(ref Product product)
        {
            if (HasThisProduct(ref product))
            {
                return Update(product);
            }
            return Save(ref product);
        }

        private bool Save(ref Product product)
        {
            try
            {
                Connection.Open();
                string query = "INSERT INTO Product VALUES(@name,@buyUnitPrice,@sellUnitPrice)";
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@name", SqlDbType.VarChar);
                Command.Parameters.Add("@buyUnitPrice", SqlDbType.Real);
                Command.Parameters.Add("@sellUnitPrice", SqlDbType.Real);
                Command.Parameters["@name"].Value = product.Name;
                Command.Parameters["@buyUnitPrice"].Value = product.BuyUnitPrice;
                Command.Parameters["@sellUnitPrice"].Value = product.SaleUnitPrice;
                Command.ExecuteNonQuery();
                Connection.Close();
                product.Id = GetLastInsertedId();
                return true;
                

            }
            catch (Exception exception)
            {
                
                throw new Exception("System Error Occurred while attempting product insertion.",exception);
            }
            finally
            {
                CloseConnection(Connection);
            }
        }

                private int GetLastInsertedId()
        {
            try
            {
                int lastId = 0;
                string query = "SELECT * FROM Product ORDER BY Id DESC";
                Command.CommandText = query;
                Connection.Open();
                using (SqlDataReader dataReader = Command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        lastId = int.Parse(dataReader["Id"].ToString());
                        break;
                    }
                }
                Connection.Close();
                return lastId;
            }
            catch (Exception exception)
            {
                
                throw  new Exception("System error,No product is saved",exception);
            }
            finally
            {
                CloseConnection(Connection);
            }
           
            
        }

        private bool Update(Product product)
        {
            try
            {
                Connection.Open();
                string query = "Update Product SET SaleUnitPrice=@saleUnitPrice, BuyUnitPrice = @buyUnitPrice WHERE Id=@productId";
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@productId", SqlDbType.Int);
                Command.Parameters["@productId"].Value = product.Id;
                Command.Parameters.Add("@saleUnitPrice", SqlDbType.Real);
                Command.Parameters["@saleUnitPrice"].Value = product.SaleUnitPrice;
                Command.Parameters.Add("@buyUnitPrice", SqlDbType.Real);
                Command.Parameters["@buyUnitPrice"].Value = product.BuyUnitPrice;
                Command.ExecuteNonQuery();
                Connection.Close();
                return true;

            }
            catch (Exception exception)
            {
                
                throw new Exception("System Error occurred while trying to update Product setup",exception);
            }
            finally
            {
                CloseConnection(Connection);
            }
        }

        private bool HasThisProduct(ref Product product)
        {
            List<Product> allProducts = GetAll();
            bool hasThisProduct = false;
            foreach (var aProduct in allProducts)
            {
                if (aProduct.Name == product.Name)
                {
                    hasThisProduct = true;
                    product.Id = aProduct.Id;
                }
            }
            return hasThisProduct;
        }
    }
}